﻿using AccessCompare.Entities;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Linq;
using System.Text;

namespace AccessCompare
{
    /// <summary>
    /// Reads the schema form the provided database
    /// </summary>
    internal class SchemaReader
    {
        #region VARIABLE DECLARATIONS

        /// <summary>
        /// Database Path
        /// </summary>
        private string DatabasePath { get; set; }

        #endregion

        #region PUBLIC PROPERTIES

        /// <summary>
        /// Gets the discovered schema from database
        /// </summary>
        public List<Table> Schema { get; private set; }

        #endregion

        #region CONSTRUCTOR

        /// <summary>
        /// Shema Reader that will populate the Table's from database
        /// </summary>
        /// <param name="dataBasePath">The path of the DB file</param>
        public SchemaReader(string dataBasePath)
        {
            Schema = new List<Table>();
            DatabasePath = dataBasePath;
        }

        #endregion

        #region PUBLIC METHODS

        /// <summary>
        /// Loads the schema from the database
        /// </summary>
        public void Load()
        {
            using (OleDbConnection conncetion = GetConnection())
            {
                OleDbCommand cmd = new OleDbCommand();
                cmd.CommandType = System.Data.CommandType.TableDirect;
                cmd.Connection = conncetion;
                cmd.Parameters.Clear();

                List<string> tableNames = new List<string>();

                if (cmd.Connection.State != System.Data.ConnectionState.Open)
                {
                    cmd.Connection.Open();
                }

                var tablesSchema = cmd.Connection.GetSchema("Tables");
                var tables = tablesSchema.AsEnumerable().Select(dr => dr.Field<string>("TABLE_NAME")).ToList();

                foreach (string tableName in tables)
                {
                    if (!tableName.Contains("MSys"))
                    {
                        Table table = new Table();
                        table.Name = tableName;

                        cmd.CommandText = GetSelectSting(tableName);

                        OleDbDataReader readTableColumns = cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly);

                        DataTable schemaTable = readTableColumns.GetSchemaTable();
                        foreach (DataRow row in schemaTable.Rows)
                        {
                            table.AddColumn(row[0].ToString(), GetOleDbType(Type.GetType(Type.GetType(row[5].ToString()).FullName)));
                        }

                        readTableColumns.Close();

                        Schema.Add(table);
                    }

                }


            }
        }

        #endregion

        #region PRIVATE METHODS

        /// <summary>
        /// Creates representing connection string
        /// </summary>
        /// <returns>Valid connection string for the passed DB</returns>
        private string GetConnectionString()
        {
            if (!string.IsNullOrEmpty(DatabasePath))
            {
                return string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", DatabasePath);
            }
            else
            {
                throw new Exception("DataBase path is empty!");
            }
        }

        /// <summary>
        /// Gets the connection
        /// </summary>
        /// <returns>OleDbConnection used for querying the DB</returns>
        private OleDbConnection GetConnection()
        {
            return new OleDbConnection(GetConnectionString());
        }

        /// <summary>
        /// Gets the select string from where the schema will be extracted for the table
        /// </summary>
        /// <param name="tableName">Table for wich schema will be extracted</param>
        /// <returns>String that will be used for retriving schema</returns>
        private string GetSelectSting(string tableName)
        {
            return string.Format("SELECT * FROM {0}", tableName);
        }

        /// <summary>
        /// Converts the system type to OleDbType
        /// </summary>
        /// <param name="sysDbType">System Type that needs to be converted</param>
        /// <returns>Coresponding OleDbType</returns>
        private OleDbType GetOleDbType(Type sysDbType)
        {
            if (sysDbType == typeof(String))
            {
                return OleDbType.VarChar;
            }
            else if (sysDbType == typeof(int))
            {
                return OleDbType.Integer;
            }
            else if (sysDbType == typeof(Boolean))
            {
                return OleDbType.Boolean;
            }
            else if (sysDbType == typeof(DateTime))
            {
                return OleDbType.Date;
            }
            else if (sysDbType == typeof(Char))
            {
                return OleDbType.Char;
            }
            else if (sysDbType == typeof(Decimal))
            {
                return OleDbType.Decimal;
            }
            else if (sysDbType == typeof(Double))
            {
                return OleDbType.Double;
            }
            else if (sysDbType == typeof(Single))
            {
                return OleDbType.Single;
            }
            else if (sysDbType == typeof(Byte))
            {
                return OleDbType.Binary;
            }
            else if (sysDbType == typeof(Guid))
            {
                return OleDbType.Guid;
            }
            else
                return OleDbType.Currency;

        }

        #endregion
    }
}
